package HomeWork.StudentMax;

import HomeWork.Class.Classbean;
import HomeWork.Class.StudentBean;
import HomeWork.Util.GUIUtil;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;

public class StudentManger {
    private StudentManger() {}
    public static List<StudentBean> getAllStudent(){
        List<StudentBean> list=new ArrayList<StudentBean>();
        try {
            Connection connection=GUIUtil.getConnection();
            String sql= "select stu.s_id as stuId,\n" +
                    "       stu.s_name as stuName,\n" +
                    "       stu.s_age as stuAge,\n" +
                    "       stu.s_gender as stuGender,\n" +
                    "       stu.s_photo as stuPhoto,\n" +
                    "       cls.cid as clsId,\n" +
                    "       cls.cname as clsName\n" +
                    "       from t_student as stu left join t_class as cls on stu.s_class_id = cls.cid";
            PreparedStatement preparedStatement=connection.prepareStatement(sql);
            ResultSet resultSet=preparedStatement.executeQuery();
            while (resultSet.next()){
                StudentBean studentBean=new StudentBean();
                studentBean.setId(resultSet.getInt("stuId"));
                studentBean.setName(resultSet.getString("stuName"));
                studentBean.setAge(resultSet.getInt("stuAge"));
                studentBean.setGender(resultSet.getString("stuGender"));
                studentBean.setPhoto(resultSet.getString("stuPhoto"));
                Classbean classBean = new Classbean();
                classBean.setId(resultSet.getInt("clsId"));
                classBean.setName(resultSet.getString("clsName"));
                studentBean.setClassbean(classBean);
                list.add(studentBean);
            }
        }catch (Exception e){
            e.printStackTrace();
        }finally {
            GUIUtil.close();
        }

        return list;
    }
    public static StudentBean getStudentOne(int studentid){
        StudentBean studentBean=null;
        try {
            ResultSet resultSet=GUIUtil.cha("select stu.s_id as stuId,\n" +
                    "       stu.s_name as stuName,\n" +
                            "        stu.s_age as stuAge,\n" +
                            "        stu.s_gender as stuGender,\n" +
                            "        stu.s_photo as stuPhoto,\n" +
                            "        cls.cid as clsId,\n" +
                            "        cls.cname as clsName\n" +
                            "        from t_student as stu left join t_class as cls on stu.s_class_id = cls.cid\n" +
                            "         where stu.s_id=?",studentid);
            while (resultSet.next()){
                studentBean=new StudentBean();
                studentBean.setId(resultSet.getInt("stuId"));
                studentBean.setName(resultSet.getString("stuName"));
                studentBean.setAge(resultSet.getInt("stuAge"));
                studentBean.setGender(resultSet.getString("stuGender"));
                studentBean.setPhoto(resultSet.getString("stuPhoto"));
                Classbean classBean = new Classbean();
                classBean.setId(resultSet.getInt("clsId"));
                classBean.setName(resultSet.getString("clsName"));
                studentBean.setClassbean(classBean);

            }
        }catch (Exception e){
            e.printStackTrace();
        }finally {
            GUIUtil.close();
        }
        return studentBean;
    }
    public static int addStudent(StudentBean studentBean){
        int num=0;
        num=GUIUtil.zengshangaicha("insert into t_student(s_name,s_age,s_gender,s_photo,s_class_id) values(?,?,?,?,?)",
                new Object[]{studentBean.getName(),studentBean.getAge(),studentBean.getGender(),studentBean.getPhoto(),
                studentBean.getClassbean().getId()});
        return num;
    }
    public static int deleteStudent(StudentBean studentBean) {
        int num = 0;
        num = GUIUtil.zengshangaicha("delete from t_student where s_id=?",new Object[] {studentBean.getId()});
        return num;
    }
    public static int updateStudent(StudentBean studentBean){
        int num=0;
        num=GUIUtil.zengshangaicha("update t_student set s_name=?,s_age=?,s_gender=?,s_photo=?,s_class_id=? where s_id =?",new Object[]{studentBean.getName(),studentBean.getAge(),studentBean.getGender(),studentBean.getPhoto(),studentBean.getClassbean().getId(),studentBean.getId()});
        return num;
    }
}
